Imports System.Data.SqlClient
Imports clsPNATTH
Imports clsPNATTH.clsOmega
Public Class clsHRUser
#Region "Variables"
    Private _strIDEmployee As String
    Private _strUserName As String
    Private _strPassword As String
    Private _bolAdmin As Boolean
    Private _dsData As DataSet
	
	'pbnG them moi
	private _pbng1 as string
'th?m
private _pbng2 as string
#End Region
#Region "Properties"
    Public Property IDEmployee() As String
        Get
            Return _strIDEmployee
        End Get
        Set(ByVal value As String)
            _strIDEmployee = value
        End Set
    End Property
    Public Property UserName() As String
        Get
            Return _strUserName
        End Get
        Set(ByVal value As String)
            _strUserName = value
        End Set
    End Property
    Public Property Password() As String
        Get
            Return _strPassword
        End Get
        Set(ByVal value As String)
            _strPassword = value
        End Set
    End Property
    Public Property Admin() As Boolean
        Get
            Return _bolAdmin
        End Get
        Set(ByVal value As Boolean)
            _bolAdmin = value
        End Set
    End Property
#End Region
#Region "Methods"
    Public Shared Function IsExistEmployee(ByVal strIDEmployee As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT Top 1 IDEmployee FROM HREmployee WHERE IDEmployee=N'" & strIDEmployee.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader()
            If oDataReader.Read Then
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return True
            Else
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return False
            End If
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.IsExistEmployee)", , True)
        End Try
    End Function
    Public Shared Function IsExist(ByVal strUserName As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT Top 1 UserName FROM HRUser WHERE UserName=N'" & strUserName.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader()
            If oDataReader.Read Then
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return True
            Else
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return False
            End If
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.IsExist)", , True)
        End Try
    End Function
    Public Shared Function IsExistIDEmployee(ByVal strIDEmployee As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT Top 1 IDEmployee FROM HRUser WHERE IDEmployee=N'" & strIDEmployee.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader()
            If oDataReader.Read Then
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return True
            Else
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return False
            End If
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.IsExistIDEmployee)", , True)
        End Try
    End Function
    Public Shared Function IsExistUser(ByVal strUserName As String, ByVal strPassword As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT Top 1 UserName FROM HRUser WHERE UserName=N'" & strUserName.Trim & "' AND Password=N'" & strPassword.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader()
            If oDataReader.Read Then
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return True
            Else
                oDataReader.Close() : oDataReader = Nothing
                oCommand.Dispose() : oCommand = Nothing
                oConnect.CloseConnection() : oConnect = Nothing
                Return False
            End If
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.IsExistUser)", , True)
        End Try
    End Function
    Public Function Add() As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand
            oCommand.CommandText = "INSERT INTO HRUser " & _
                                   "VALUES(N'" & _strIDEmployee & "',N'" & _strUserName & "',N'" & _strPassword & "'," & _
                                   CInt(_bolAdmin) & ")"
            oCommand.Connection = oConnect.GetConnectionOHR
            oCommand.ExecuteNonQuery()
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
            Return True
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.Add)", , True)
        End Try
    End Function
    Public Shared Function Delete(ByVal strUserName As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand
            oCommand.CommandText = "DELETE FROM HRUser WHERE UserName=N'" & strUserName.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oCommand.ExecuteNonQuery()
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
            Return True
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.Delete)", , True)
        End Try
    End Function
    Public Function Update() As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand
            oCommand.CommandText = "UPDATE HRUser SET " & _
                                   "IDEmployee=N'" & _strIDEmployee & "',Password=N'" & _strPassword & "',Admin=" & CInt(_bolAdmin) & "  " & _
                                   "WHERE UserName=N'" & _strUserName & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oCommand.ExecuteNonQuery()
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.Update)", , True)
        End Try
    End Function
    Public Function GetTable() As DataTable
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataAdapter As New SqlDataAdapter
            If _dsData Is Nothing Then _dsData = New DataSet
            oCommand.CommandText = "SELECT HRUse.IDEmployee,HRUse.UserName,HRUse.Password,HRUse.Admin,HREmp.LastName+' '+HREmp.FirstName,HRJob.JName " & _
                                   "FROM HRUser HRUse " & _
                                   "LEFT OUTER JOIN HREmployee HREmp ON HREmp.IDEmployee=HRUse.IDEmployee " & _
                                   "LEFT OUTER JOIN HRJobTitle HRJob ON HRJob.IDJobTitle=HREmp.IDJobTitle " & _
                                   "ORDER BY HRUse.IDEmployee ASC"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataAdapter.SelectCommand = oCommand
            oDataAdapter.Fill(_dsData)
            oDataAdapter.Dispose() : oDataAdapter = Nothing
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
            Return _dsData.Tables(0)
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.GetTable)", , True)
        End Try
        Return Nothing
    End Function
    Public Function Search(ByVal strField As String, ByVal strValue As String) As DataTable
        Try
            'Dim oConnect As New clsDataBase
            'Dim oCommand As New SqlCommand, oDataAdapter As New SqlDataAdapter
            'If _dsData Is Nothing Then _dsData = New DataSet
            'Select Case strField
            '    Case "UName"
            '        oCommand.CommandText = "SELECT * FROM HRUser " & _
            '                               "WHERE " & strField & " Like N'%" & strValue.Trim & "%' " & _
            '                               "ORDER BY IDEmployee ASC"
            '    Case "EmployeeName"
            '        oCommand.CommandText = "SELECT HRUse.* " & _
            '                               "FROM HREmployee HREmp,HRUser HRUse " & _
            '                               "WHERE HREmp.FirstName LIKE N'%" & strValue & "%' AND HRUse.IDEmployee=HREmp.IDEmployee " & _
            '                               "ORDER BY HRUse.IDEmployee ASC"
            'End Select
            'oCommand.Connection = oConnect.GetConnectionOHR
            'oDataAdapter.SelectCommand = oCommand
            'oDataAdapter.Fill(_dsData)
            'oDataAdapter.Dispose() : oDataAdapter = Nothing
            'oCommand.Dispose() : oCommand = Nothing
            'oConnect.CloseConnection() : oConnect = Nothing
            'Return _dsData.Tables(0)
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.Search)", , True)
        End Try
        Return Nothing
    End Function
    Public Function GetInfor(ByVal strUserName As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT * FROM HRUser WHERE UserName=N'" & strUserName.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader
            If oDataReader.Read Then
                _setInfor(oDataReader)
            End If
            oDataReader.Close() : oDataReader = Nothing
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.GetInfor)", , True)
        End Try
    End Function
    Public Function GetInforEmployee(ByVal strIDEmployee As String) As Boolean
        Try
            Dim oConnect As New clsDataBase
            Dim oCommand As New SqlCommand, oDataReader As SqlDataReader
            oCommand.CommandText = "SELECT * FROM HRUser WHERE IDEmployee=N'" & strIDEmployee.Trim & "'"
            oCommand.Connection = oConnect.GetConnectionOHR
            oDataReader = oCommand.ExecuteReader
            If oDataReader.Read Then
                _setInfor(oDataReader)
            End If
            oDataReader.Close() : oDataReader = Nothing
            oCommand.Dispose() : oCommand = Nothing
            oConnect.CloseConnection() : oConnect = Nothing
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.GetInforEmployee)", , True)
        End Try
    End Function
    Private Sub _setInfor(ByVal oDataReader As SqlDataReader)
        Try
            Try
                _strIDEmployee = oDataReader.GetString(0)
            Catch ex As Exception
                _strIDEmployee = ""
            End Try
            Try
                _strUserName = oDataReader.GetString(1)
            Catch ex As Exception
                _strUserName = ""
            End Try
            Try
                _strPassword = oDataReader.GetString(2)
            Catch ex As Exception
                _strPassword = ""
            End Try
            Try
                _bolAdmin = oDataReader.GetBoolean(3)
            Catch ex As Exception
                _bolAdmin = False
            End Try
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser._setInfor)", , True)
        End Try
    End Sub
    Public Function DisposedUser() As Boolean
        Try
            _strIDEmployee = Nothing
            _strUserName = Nothing
            _strPassword = Nothing
            _bolAdmin = Nothing
            If Not _dsData Is Nothing Then _dsData.Dispose()
            _dsData = Nothing
        Catch ex As Exception
            ShowInfor(Nothing, "", ex.Message.ToString & "(clsHRUser.DisposedUser)", , True)
        End Try
    End Function
    Public Shared Function SQLGetTable() As String
        SQLGetTable = "SELECT * FROM HRUser ORDER BY IDEmployee ASC"
    End Function
    Public Shared Function SQLSelect() As String
        SQLSelect = "SELECT * FROM HRUser WHERE "
    End Function
    Public Shared Function SQLOrder() As String
        SQLOrder = "ORDER BY IDEmployee ASC"
    End Function
#End Region
End Class
